SharePoint Lists & PowerApps - Issues with Calculated Columns of Type “Number”

PowerApps is receiving calculated columns of type Number in the wrong format (Photo by the author, illustrations by Takashi Mifune under free use)

When creating Canvas PowerApps, developers might face the requirement of including values of SharePoint’s Calculated Columns eventually. However, if you have worked with PowerApps before, you might know that the data types are not consistent between SharePoint and Power Apps when it comes to the type Number. That situation can leave developers with quite the headache:

"Help! — I have created a Calculated Column in a SharePoint List, but when I want to display the value inside my Power App, a lot of trailing numbers are added. What can I do?"

• Someone working with Calculated columns of type Number in PowerApps

There is a simple solution: Convert the property to a data format Power Apps can work with. Depending on your setup, there are two different approaches.

• Fix the issue in SharePoint (recommended): If data consistency is not required, change the returned data type to Text.
• Fix the issue in PowerApps: If data consistency is required, data types should be manually converted in PowerApps.

Please take a look at the following decision Flow Chart:

Decision chart on what to do when affected by Calculcated Columns of type Number in Power Apps (Photo by the author)

The following step-by-step guide will walk you through how to handle trailing zero issues with Calculated SharePoint Columns in PowerApps:

Solutions

Solution 1: Format the Number in SharePoint and deliver type “Text” (recommended)

The best approach is to avoid the bug altogether. In detail, that means, converting the Number to Text in SharePoint right away and serving the column as type “Text”.

This approach is only suitable if data type consistency within your solution is not important. When other systems correct data type handling access SharePoint, other issues will arise.

The necessary changes are reflected in this comparison:

Comparision of before and after (Photo by the author)

Solution 2: Use Value() + Round()

We can use Value() to convert a Text value to type Number. We can use Round() to remove any trailing zeros in an accurate way.

Microsoft Power Fx:

Round(
    Value(ThisItem.CalculatedColumnName),
    2
)

Solution 3: Value() + Text()

We can utilize Value() to convert Text value to type Number. With Text(), we can then format the Number how we would like to display it inside our application.

Microsoft Power Fx:

Text(
    Value(ThisItem.CalculatedColumnName),
    "[$-en-US]#,#.00"
)

Conclusion

It’s very much possible to still use Calculated Columns, even when values of type Number are involved. That said, Power Apps doesn’t do data type conversion out-of-the-box right, and some requirements require some workarounds.

I personally hope that data type handling will be improved within Power Apps. Until then, the ways described in this article is the best we have.